package com.ml.dao.impl;

import com.ml.dao.DkAccessDao;
import com.ml.dao.KqAccessBaseDao;
import com.ml.model.KqData;
import org.springframework.util.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 查看打卡的实现dao
 */
public class DkAccessDaoImpl extends KqAccessBaseDao implements DkAccessDao {

    /**
     * 上海 某人 指定时间范围 的打卡记录
     * @param startdate 类似 2019/6/2 的格式 这是和考勤机 的格式保持一致的
     * @param enddate
     * @param user
     * @return
     */
    public  List<KqData> getDkRecordSh(String startdate, String enddate, String user){
        // access  支持这种格式的时间 2019/6/01            06:59:59
        String sql = "";
        if(StringUtils.hasText(enddate)){//说明是指定时间段
            sql = " SELECT HolderName,HolderNo,IODate,IOtime FROM IOData where IODate >= cdate('"+startdate+"') and IODate <= cdate('"+enddate+"') order by IOTime desc";
        }else if(StringUtils.hasText(user)){//说明是指定 用户 和 时间段
            sql = " SELECT HolderName,HolderNo,IODate,IOtime FROM IOData where HolderName='"+user+"' and IODate >= cdate('"+startdate+"') and IODate <= cdate('"+enddate+"') order by IOTime desc ";
        }else{ //默认查询指定天数的
            sql = " SELECT HolderName,HolderNo,IODate,IOtime FROM IOData where IODate = cdate('"+startdate+"') order by IOTime desc ";
        }

        List<KqData> list = new ArrayList<KqData>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = this.openConnectSh();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                //添加到list中去
                //String userName = rs.getString("HolderName");//考勤名字
                String signDate = rs.getString("IODate");
                String signTime = rs.getString("IOtime");
                String HolderName = rs.getString("HolderName");//用户名字
                KqData kqData = new KqData(HolderName,signDate,signTime);
                list.add(kqData);
            }
            this.closeConnect(conn, null, ps, rs);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }


    /**
     * 大连某人 指定时间范围 的打卡记录
     * @param startdate 类似 2019/6/2 的格式 这是和考勤机 的格式保持一致的
     * @param enddate
     * @param user
     * @return
     */
    public  List<KqData> getDkRecordDl(String startdate, String enddate, String user){
        String sql = "";
        if(StringUtils.hasText(enddate)){//说明是指定时间段
            sql = " SELECT temp.name as name ,temp.dkTime as dkTime FROM(" +
                    " SELECT c.f_ConsumerName as name ,a.f_OnDuty1 as dkTime FROM t_d_shift_AttReport a INNER JOIN t_b_Consumer c ON a.f_ConsumerID = c.f_ConsumerID"+
                    " UNION"+
                    " SELECT c.f_ConsumerName as name , a.f_OffDuty1 as dkTime FROM t_d_shift_AttReport a INNER JOIN t_b_Consumer c ON a.f_ConsumerID = c.f_ConsumerID"+
                    " ) temp where temp.dkTime is not null "+
                    " AND  dkTime>=cdate('"+startdate+" 00:00:00')"+
                    " AND  dkTime<=cdate('"+enddate+" 23:59:59')"+
                    " ORDER BY temp.dkTime DESC";
        }else if(StringUtils.hasText(user)){//说明是指定 用户 和 时间段
            sql = " SELECT temp.name as name ,temp.dkTime as dkTime FROM(" +
                    " SELECT c.f_ConsumerName as name ,a.f_OnDuty1 as dkTime FROM t_d_shift_AttReport a INNER JOIN t_b_Consumer c ON a.f_ConsumerID = c.f_ConsumerID"+
                    " UNION"+
                    " SELECT c.f_ConsumerName as name , a.f_OffDuty1 as dkTime FROM t_d_shift_AttReport a INNER JOIN t_b_Consumer c ON a.f_ConsumerID = c.f_ConsumerID"+
                    " ) temp where temp.dkTime is not null "+
                    " and temp.name='"+user+"'"+
                    " AND  dkTime>=cdate('"+startdate+" 00:00:00')"+
                    " AND  dkTime<=cdate('"+enddate+" 23:59:59')"+
                    " ORDER BY temp.dkTime DESC";
        }else{ //默认查询指定天数的
            sql = " SELECT temp.name as name ,temp.dkTime as dkTime FROM(" +
                    " SELECT c.f_ConsumerName as name ,a.f_OnDuty1 as dkTime FROM t_d_shift_AttReport a INNER JOIN t_b_Consumer c ON a.f_ConsumerID = c.f_ConsumerID"+
                    " UNION"+
                    " SELECT c.f_ConsumerName as name , a.f_OffDuty1 as dkTime FROM t_d_shift_AttReport a INNER JOIN t_b_Consumer c ON a.f_ConsumerID = c.f_ConsumerID"+
                    " ) temp where temp.dkTime is not null "+
                    " AND  dkTime>=cdate('"+startdate+" 00:00:00')"+
                    " AND  dkTime<=cdate('"+startdate+" 23:59:59')"+
                    " ORDER BY temp.dkTime DESC";
        }

        List<KqData> list = new ArrayList<KqData>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = this.openConnectDl();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                //添加到list中去
                String userName = rs.getString("name");//用户名字
                String checktime = rs.getString("dkTime");//打卡时间
                String signDate = "";
                String signTime = "";
                if(StringUtils.hasText(checktime)){
                    String times[] = checktime.split(" ");
                    signDate = times[0];
                    signTime = times[1];
                }
                KqData kqData = new KqData(userName,signDate,signTime);
                list.add(kqData);
            }
            this.closeConnect(conn, null, ps, rs);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }


    /**
     * 天津 某人 指定时间范围 的打卡记录
     * @param startdate 类似 2019/6/2 的格式 这是和考勤机 的格式保持一致的
     * @param enddate
     * @param user
     * @return
     */
    public  List<KqData> getDkRecordTj(String startdate, String enddate, String user){
        // access  支持这种格式的时间 2019/6/01            06:59:59
        String sql = " ";
        if(StringUtils.hasText(enddate)){//说明是指定时间段
            sql = " SELECT u.name as name,c.checktime as checktime FROM userinfo  u , checkinout c where c.userid = u.userid and u.name not like '临时%'  and c.checktime >=cdate('"+startdate+" 00:00:00') and c.checktime <=cdate('"+enddate+" 23:59:59') order by checktime desc";
        }else if(StringUtils.hasText(user)){//说明是指定 用户 和 时间段
            sql = " SELECT u.name as name,c.checktime as checktime FROM userinfo  u , checkinout c where c.userid = u.userid and u.name = '"+user+"'  and u.name not like '临时%'  and c.checktime >=cdate('"+startdate+" 00:00:00') and c.checktime <=cdate('"+enddate+" 23:59:59') order by checktime desc";
        }else{ //默认查询指定天数的
            sql = " SELECT u.name as name,c.checktime as checktime FROM userinfo  u , checkinout c where c.userid = u.userid and u.name not like '临时%'  and c.checktime >=cdate('"+startdate+" 00:00:00') and c.checktime <=cdate('"+startdate+" 23:59:59') order by checktime desc";
        }
        List<KqData> list = new ArrayList<KqData>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = this.openConnectTj();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                //添加到list中去
                //String userName = rs.getString("HolderName");//考勤名字
                String checktime = rs.getString("checktime");
                String signDate = "";
                String signTime = "";
                if(StringUtils.hasText(checktime)){
                    String times[] = checktime.split(" ");
                    signDate = times[0];
                    signTime = times[1];
                }
                String userName = rs.getString("name");//用户名字
                KqData kqData = new KqData(userName,signDate,signTime);
                list.add(kqData);
            }
            this.closeConnect(conn, null, ps, rs);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }




       /**
     * 获取 所有人 指定日期 打卡记录
     * @param date 类似 2019/6/2 的格式 这是和考勤机 的格式保持一致的
     * @return
     */
    public List<KqData> getDkRecordSh(String date){
        return getDkRecordSh(date,"","");
    }
    /**
     * 获取 所有人 指定日期范围 打卡记录
     * @param startdate 类似 2019/6/2 的格式 这是和考勤机 的格式保持一致的
     * @param enddate
     * @return
     */
    public List<KqData> getDkRecordSh(String startdate, String enddate){
        return getDkRecordSh(startdate,enddate,"");
    }




    public List<KqData> getDkRecordTj(String date){
        return getDkRecordTj(date,"","");
    }
    public List<KqData> getDkRecordTj(String startdate, String enddate){
        return getDkRecordTj(startdate,enddate,"");
    }





    public List<KqData> getDkRecordDl(String date){
        return getDkRecordDl(date,"","");
    }
    public List<KqData> getDkRecordDl(String startdate, String enddate){
        return getDkRecordDl(startdate,enddate,"");
    }

}
